package com.scau.wrongtitle.repository;

import com.scau.wrongtitle.domin.WrongTitle;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface WrongTitleRepository extends JpaRepository<WrongTitle, Long> {
    @Query(value = "select * from " +
            "(select * from t_wrong_title as t_w where t_w.question_grade = :grade and t_w.question_subject= :subject) as T, " +
            "(select * from t_title_record as t_r where t_r.user_id = :id) as S where T.question_id = S.question_id and S.is_del is false ;", nativeQuery = true)
    List<WrongTitle> getUserTitle(@Param("id") long id, @Param("grade") String grade, @Param("subject")String subject);

    @Query(value = "select question_subject,count(*) as subject_sum from (select * from t_wrong_title as t_w where t_w.question_grade = :grade) as T, " +
            "(select * from t_title_record as t_r where t_r.user_id = :id) as S " +
            "where T.question_id = S.question_id " +
            "group by question_subject;", nativeQuery = true)
    List<Map<String, Object>> getUserSubjects(@Param("id") long id, @Param("grade") String grade);

    WrongTitle findByQuestionId(long id);
}